from bs4 import BeautifulSoup
import re
import db
import pymysql

with open('data/music.html','r',encoding='utf-8') as f:
    html = f.read()
    # print(html)
    bs = BeautifulSoup(html,'lxml')
    id_cs = bs.select('table.m-table tbody td.left span.ply')
    ids = []
    names = []
    times = []
    hrefs = []
    singers = []
    albums = []

    for tr in bs.select("table.m-table tbody tr"):
        ids.append(tr.select_one('td.left span.ply').get('data-res-id'))
        anode = tr.select_one('div.f-cb a')
        # thref = anode.get('href')
        # re_str = r'/song\?id=(\d+)'
        # tid = re.search(re_str,thref).group(1)
        # print(tid)
        hrefs.append(f'https://music.163.com/#{anode.get("href")}')
        names.append(anode.select_one('b').get('title').replace('\xa0', ' '))
        mnode = tr.select_one('td.s-fc3 span.u-dur').text
        times.append(mnode.strip())
        singers.append(tr.select_one('div.text span').get('title'))
        albums.append(tr.select_one('td:nth-child(5) a').get('title').replace('\xa0',' '))

    # for idc in id_cs:
    #     ids.append(idc.get('data-res-id'))
    # for nc in bs.select('table.m-table tbody div.f-cb a'):
    #     #nc就是一个a节点，可以继续通过a节点来进行select
    #     hrefs.append(f'https://music.163.com/#/{nc.get("href")}')
    #     names.append(nc.select_one('b').get('title').replace('\xa0',' '))

    # print(len(ids),ids)
    # print(len(hrefs),hrefs)
    # print(len(names),names)
    # print(len(times),times)
    # print(len(singers),singers)
    # print(len(albums),albums)

def check_song(id):
    con = db.get_con()
    try:
        with con.cursor() as cursor:
            sql = 'select * from music_163 where song_id=%s'
            cursor.execute(sql,(id,)) #执行sql
            rs = cursor.fetchone()
            return rs
    finally:
        db.close_con(con)
# check_song('1911442482')
def add_music():
    con = db.get_con()
    try:
        for id,href,name,time,singer,album in zip(ids,hrefs,names,times,singers,albums):
            if(check_song(id)): #存在就不添加
                continue
            with con.cursor() as cursor:
                sql = 'insert into music_163 (song_id,href,name,times,singer,album,is_song) value (%s,%s,%s,%s,%s,%s,%s)'
                cursor.execute(sql,(id,href,name,time,singer,album,0))
                con.commit()
    finally:
        db.close_con(con)

add_music()